package io.github.biezhi.elves.mapper;

import java.util.List;

import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import com.github.pagehelper.Page;

import io.github.biezhi.elves.model.BulletinCourt;
import io.github.biezhi.elves.model.ChangeInfo;
import io.github.biezhi.elves.model.EbaseInfo;
import io.github.biezhi.elves.model.EnterpriseProfileInfo;
import io.github.biezhi.elves.model.ExecutorInfo;
import io.github.biezhi.elves.model.FamilyInvestmentInfo;
import io.github.biezhi.elves.model.LegalInfo;
import io.github.biezhi.elves.model.LegalInfoHis;
import io.github.biezhi.elves.model.MainPersonnel;
import io.github.biezhi.elves.model.NoticeCourt;
import io.github.biezhi.elves.model.RefereeDoc;
import io.github.biezhi.elves.model.RelationProfileInfo;
import io.github.biezhi.elves.model.Shareholder;
import io.github.biezhi.elves.model.StockStructureInfo;
import io.github.biezhi.elves.model.StockStructureProfileInfo;

/**
 * 股东数据
 *
 */
public interface ShareholderMapper {
	/*******************************查询股东数据 开始*********************************************************/
	/**
	 * 查询还没爬取企业列表数据
	 * @return
	 */
	@Select("select count(1) from li_crawl_table_data t where t.is_load = 'F' and t.is_null = 'F'")
	public int countCrawlTableDataForNotLoad();
	
	/**
	 * 分页查询爬取列表数据
	 * @return
	 */
	@Select("select * from li_crawl_table_data t where t.is_load = 'F' and t.is_null = 'F'")
	public Page<String> queryCrawlTableDataPage();
	
	/**
	 * 分页查询根据地市ID爬取列表数据
	 * @return
	 */
	@Select("select t.entname from li_crawl_table_data t where t.is_load = 'F' and t.is_null = 'F' and t.city_id = #{cityId}")
	public Page<String> queryCrawlTableDataPageByCityId(String cityId);
	
	/**
	 * 根据企业名称查询企业基础表数据
	 * @param entname
	 * @return
	 */
	@Select("select t.pripid,t.entname,t.regorg from e_baseinfo t where t.entname = #{entname}")
	public List<EbaseInfo> queryEbaseInfoByEntname(String entname);
	
	/**
	 * 查询企业基础表空数据
	 * @return
	 */
	@Select("select t.pripid,t.entname from e_baseinfo t where t.entname is null or t.entname = '' or t.entname = 'null'")
	public Page<EbaseInfo> queryEbaseInfoEntnameIsNull();
	
	/**
	 * 统计企业基础表空数据数量
	 * @return
	 */
	@Select("select count(1) from e_baseinfo t where t.entname is null or t.entname = '' or t.entname = 'null'")
	public int countEbaseInfoEntnameIsNull();
	
	/**
	 * 根据unique_id查询法人代表信息表entname(企业名称)字段
	 * @param uniqueId
	 * @return
	 */
	@Select("select t.entname from app_legal_info t where t.unique_id = #{uniqueId} limit 1")
	public LegalInfo queryEntnameByUnique(String uniqueId);
	
	/**
	 * 统计去重企业名称法人代表信息表数据量
	 * @return
	 */
	@Select("select count(distinct t.entname) from app_legal_info t")
	public int countAppLegalInfoForDistinctEntname();
	
	/**
	 * 获取要爬取企业列表数量
	 * @return
	 */
	@Select("select * from li_paiqu_number")
	public int getPaiquNumber();
	
	/**
	 * 获取要工商列表数据(分页)
	 * @return
	 */
	@Select("select * from e_baseinfo t where substring(t.regorg,1,4) = '4401'")
	public Page<EbaseInfo> queryEbaseInfoForCityIdPage();
	
	/**
	 * 查询爬取数据列表中没有更新的地市id数据统计数
	 * @param cityId
	 * @return
	 */
	@Select("select count(1) from li_crawl_table_data t where t.city_id = #{cityId}")
	public int countCrawlTableDataByCityId(String cityId);
	
	/**
	 * 将uuid作为入参，查询企业图谱表数据keyno值，是否已存在数据库中
	 * @param uuid
	 * @return
	 */
	@Select("select count(1) from app_enterprise_profile_info t where t.keyno = #{uuid}")
	public int countEnterpriseKeyNoByUuid(String uuid);
	
	/**
	 * 根据地市ID查询法人代表数据(分页)
	 * @param cityId
	 * @return
	 */
	@Select("select t.unique_id as uniqueId,t.entname,t.city_id as cityId from app_legal_info t where t.city_id = #{cityId}  and t.unique_id is not null")
	public Page<LegalInfo> getLegalInfoByCityId(String cityId);
	
	/**
	 * 查询企业图谱表总数
	 * 
	 * @param cityId
	 * @return
	 */
	@Select("select count(1) from app_enterprise_profile_info t where t.city_id = #{cityId}")
	public int countEnterpriseProfileInfo(String cityId);
	
	/**
	 * 将uuid作为入参，查询投资族谱表数据keyno值，是否已存在数据库中
	 * @param uuid
	 * @return
	 */
	@Select("select count(1) from app_family_investment_info t where t.keyno = #{uuid}")
	public int countFamilyInvestmentKeyNoByUuid(String uuid);
	
	/**
	 * 查询投资族谱表总数
	 * 
	 * @param cityId
	 * @return
	 */
	@Select("select count(DISTINCT t.entname) from app_family_investment_info t where t.city_id = #{cityId}")
	public int countFamilyInvestmentInfo(String cityId);
	/*******************************查询股东数据 结束*********************************************************/
	
	/*******************************新增股东数据 开始*********************************************************/
	/**
	 * 保存法人代表数据
	 * @param legalInfo
	 */
	@Insert("insert into app_legal_info (unique_id,pripid,legal_name,entname,regcap,reccap,jy_status,estdate,regno,organization_code,"
			+"taxpayer_iden_no,uniscid,company_type,industry,apprdate,regorg,area_cn,area_en,before_used_name,operation_mode,"
			+"personnel_scale,business_term,ent_addr,manage_scope,city_id) values "
			+"(#{uniqueId},#{pripid},#{legalName},#{entname},#{regcap},#{reccap},#{jyStatus},#{estdate},#{regno},#{organizationCode},"
			+"#{taxpayerIdenNo},#{uniscid},#{companyType},#{industry},#{apprdate},#{regorg},#{areaCn},#{areaEn},#{beforeUsedName},"
			+"#{operationMode},#{personnelScale},#{businessTerm},#{entAddr},#{manageScope},#{cityId})")
	public void saveLegalInfo(LegalInfo legalInfo);
	
	/**
	 * 批量保存法人代表数据空数据(来源于数据库e_baseinfo)
	 * @param ebaseInfo
	 */
	public void batchSaveLegalInfoIsNull(List<EbaseInfo> ebaseInfo);
	
	/**
	 * 保存股东数据
	 * @param shareholder
	 */
	@Insert("insert into app_shareholder (unique_id,pripid,entname,shareholder_name,shareholder_ratio,subscribed_capital,subscription_date,shareholder_type,city_id) values (#{uniqueId},#{pripid},#{entname},#{shareholderName},#{shareholderRatio},#{subscribedCapital},#{subscriptionDate},#{shareholderType},#{cityId})")
	public void saveShareholder(Shareholder shareholder);
	
	/**
	 * 保存股权结构
	 * @param stockStructureInfo
	 */
	@Insert("insert into app_stock_structure_info (unique_id,pripid,entname,keyno,shareholder_name,parent_keyno,fundedratio,subconamt,grade,city_id) values (#{uniqueId},#{pripid},#{entname},#{keyNo},#{Name},#{parentKeyNo},#{fundedRatio},#{subConAmt},#{Grade},#{cityId})")
	public void saveStockStructure(StockStructureInfo stockStructureInfo);
	
	/**
	 * 保存主要人员
	 * @param mainPersonnel
	 */
	@Insert("insert into app_pri_personnel (unique_id,pripid,entname,personnel_name,post,city_id) values (#{uniqueId},#{pripid},#{entname},#{personnelName},#{post},#{cityId})")
	public void saveMainPersonnel(MainPersonnel mainPersonnel);
	
	/**
	 * 保存历史法定代表人
	 * @param legalInfoHis
	 */
	@Insert("insert into app_legal_info_his (unique_id,pripid,entname,legal_name,city_id) values (#{uniqueId},#{pripid},#{entname},#{personnelName},#{cityId)")
	public void saveLegalInfoHis(LegalInfoHis legalInfoHis);
	
	/**
	 * 保存董事长或执行董事成员
	 * @param changeInfo
	 */
	@Insert("insert into app_chairman_chanage (unique_id,pripid,entname,change_date,name_changel_ago,name_changel_after,is_legal,status,city_id) values (#{uniqueId},#{pripid},#{entname},#{changeDate},#{nameChangelAgo},#{nameChangelAfter},#{isLegal},#{status},#{cityId})")
	public void saveChairmanChanage(ChangeInfo changeInfo);
	
	/**
	 * 保存高管人员
	 * @param changeInfo
	 */
	@Insert("insert into app_executive_personnel (unique_id,pripid,entname,change_date,name_changel_ago,name_changel_after,is_legal,status,city_id) values (#{uniqueId},#{pripid},#{entname},#{changeDate},#{nameChangelAgo},#{nameChangelAfter},#{isLegal},#{status},#{cityId})")
	public void saveExecutivePersonnel(ChangeInfo changeInfo);
	
	/**
	 * 保存监事数据
	 * @param changeInfo
	 */@Insert("insert into app_supervisor (unique_id,pripid,entname,change_date,name_changel_ago,name_changel_after,is_legal,status,city_id) values (#{uniqueId},#{pripid},#{entname},#{changeDate},#{nameChangelAgo},#{nameChangelAfter},#{isLegal},#{status},#{cityId})")
	public void saveSupervisor(ChangeInfo changeInfo);
	
	/**
	 * 保存名称变更
	 * @param changeInfo
	 */
	 @Insert("insert into app_name_chanage (unique_id,pripid,entname,change_date,name_changel_ago,name_changel_after,is_legal,status,city_id) values (#{uniqueId},#{pripid},#{entname},#{changeDate},#{nameChangelAgo},#{nameChangelAfter},#{isLegal},#{status},#{cityId})")
	public void saveNameChange(ChangeInfo changeInfo);
	
	/**
	 * 保存法定代表人（负责人）
	 * @param changeInfo
	 */
	 @Insert("insert into app_legal_change (unique_id,pripid,entname,change_date,name_changel_ago,name_changel_after,is_legal,status,city_id) values (#{uniqueId},#{pripid},#{entname},#{changeDate},#{nameChangelAgo},#{nameChangelAfter},#{isLegal},#{status},#{cityId})")
	 public void saveLegalChange(ChangeInfo changeInfo);
	
	/**
	 * 保存董事成员
	 * @param changeInfo
	 */
	 @Insert("insert into app_chairman_member_change (unique_id,pripid,entname,change_date,name_changel_ago,name_changel_after,is_legal,status,city_id) values (#{uniqueId},#{pripid},#{entname},#{changeDate},#{nameChangelAgo},#{nameChangelAfter},#{isLegal},#{status},#{cityId})")
	 public void saveChairmanMemberChange(ChangeInfo changeInfo);
	 
	 /**
	  * 法律诉讼信息
	  * 
	  * 保存被执行人数据
	  * @param executorInfo
	  * @throws Exception
	  */
	 @Insert("insert into app_executor_info (unique_id,pripid,entname,case_no,filing_time,court_execution,executes,city_id) values (#{uniqueId},#{pripid},#{entname},#{caseNo},#{filingTime},#{courtExecution},#{executes},#{cityId})")
	 public void saveExecutorInfo(ExecutorInfo executorInfo) throws Exception;
	 
	 /**
	  * 法律诉讼信息
	  * 
	  * 保存裁判文书数据
	  * @param refereeDoc
	  * @throws Exception
	  */
	 @Insert("insert into app_referee_doc (unique_id,pripid,entname,case_name,release_time,case_no,case_identity,court_execution,city_id) values (#{uniqueId},#{pripid},#{entname},#{caseName},#{releaseTime},#{caseNo},#{caseIdentity},#{courtExecution},#{city_id})")
	 public void saveRefereeDoc(RefereeDoc refereeDoc) throws Exception;
	 
	 /**
	  * 法律诉讼信息
	  * 
	  * 保存法院公告数据
	  * @param noticeCourt
	  */
	 @Insert("insert into app_notice_court (unique_id,pripid,entname,party,notice_type,notice_man,publish_time,city_id) values (#{uniqueId},#{pripid},#{entname},#{party},#{noticeType},#{noticeMan},#{publishTime},#{cityId})")
	 public void saveNoticeCourt(NoticeCourt noticeCourt) throws Exception;
	 
	 /**
	  * 法律诉讼信息
	  * 
	  * 保存开庭公告数据
	  * @param bulletinCourt
	  */
	 @Insert("insert into app_bulletin_court (unique_id,pripid,entname,case_no,court_date,cause_action,plaintiff,defendant,city_id) values (#{uniqueId},#{pripid},#{entname},#{caseNo},#{courtDate},#{causeAction},#{plaintiff},#{defendant},#{cityId})")
	 public void saveBulletinCourt(BulletinCourt bulletinCourt) throws Exception;
	 
	 /**
	  * 创建企业数据备份表(表名+统计数)
	  * @param tableName
	  * @param count
	  * @throws Exception
	  */
	 @Insert("create table ${tableName}${count} as select * from ${tableName}")
	 public void createTableName(@Param("tableName") String tableName,@Param("count") int count) throws Exception;
	 
	 /**
	  * 保存企业图谱数据
	  * @param enterpriseProfileInfo
	  * @throws Exception
	  */
	 @Insert("insert into app_enterprise_profile_info (unique_id,pripid,entname,keyno,short_name,parent_keyno,level,city_id) values (#{uniqueId},#{pripid},#{entname},#{KeyNo},#{ShortName},#{parentKeyNo},#{Level},#{cityId})")
	 public void saveEnterpriseProfileInfo(EnterpriseProfileInfo enterpriseProfileInfo) throws Exception;
	 
	 /**
	  * 保存投资族谱数据
	  * @param familyInvestmentInfo
	  * @throws Exception
	  */
	 @Insert("insert into app_family_investment_info (unique_id,pripid,entname,funded_amount,funded_rate,keyno,short_name,parent_keyno,level,city_id) values (#{uniqueId},#{pripid},#{entname},#{FundedAmount},#{FundedRate},#{KeyNo},#{ShortName},#{parentKeyNo},#{Level},#{cityId})")
	 public void saveFamilyInvestmentInfo(FamilyInvestmentInfo familyInvestmentInfo) throws Exception;
	 
	 /**
	  * 保存关联图谱数据
	  * @param relationProfileInfo
	  * @throws Exception
	  */
	 @Insert("insert into app_relation_profile_info (unique_id,pripid,entname,id,econ_kind,keyno,start_node,name,"
			 +"end_node,regist_capi,stock_percent,should_capi,status,role,city_id,type) values ("
			 +"#{uniqueId},#{pripid},#{entname},#{id},#{econKind},#{keyNo},#{startNode},#{name},#{endNode},"
			 + "#{registCapi},#{stockPercent},#{status},#{shouldCapi},#{role},#{cityId},#{type})")
	 public void saveRelationProfileInfo(RelationProfileInfo relationProfileInfo) throws Exception;
	 
	 /**
	  * 保存股权结构图数据
	  * @param stockStructureProfileInfo
	  * @throws Exception
	  */
	 @Insert("insert into app_stock_tructure_profile_info (unique_id,pripid,entname,keyno,oper_name,fundedratio,"
			 +"grade,max_grade,city_id) values ("
			 +"#{uniqueId},#{pripid},#{entname},#{KeyNo},#{OperName},#{fundedratio},#{Grade},"
			 + "#{MaxGrade},#{cityId})")
	 public void saveStockStructureProfileInfo(StockStructureProfileInfo stockStructureProfileInfo) throws Exception;
	/*******************************新增股东数据 结束*********************************************************/
	 
	 /*******************************修改股东数据 结束*********************************************************/
	 /**
	  * 根据企业名称更新爬取列表数据表
	  * @param entname
	  */
	 @Update("update li_crawl_table_data t set t.is_load = 'T' where t.entname = #{entname}")
	 public void updateCrawlTableData(String entname) throws Exception;
	 
	 /**
	  * 根据企业名称批量更新爬取列表数据表设置is_load字段为T(意指在企查查网站搜索到数据并入库)
	  * @param list
	  */
	 public void batchUpdateCrawlTableDataForIsLoad(List<EbaseInfo> list) throws Exception;
	 
	 /**
	  * 根据企业名称更新爬取列表数据表设置is_null字段为T(意指在企查查网站没有搜索到)
	  * @param entname
	  */
	 @Update("update li_crawl_table_data t set t.is_null = 'T' where t.entname = #{entname}")
	 public void updateCrawlTableDataForIsNull(String entname) throws Exception;
	 
	 /**
	  * 根据企业名称更新爬取列表数据表设置法律诉讼字段为数值(意指在企查查某企业是否存在法律诉讼)
	  * @param susongCount
	  * @param entname
	  */
	 @Update("update li_crawl_table_data t set t.susong_count = #{susongCount} where t.entname = #{entname}")
	 public void updateCrawlTableDataForSusongCount(String susongCount,String entname) throws Exception;
	 
	 /**
	  * 根据企业名称批量更新爬取列表数据表地市id和地市名称
	  * @param list
	  */
	 public void batchUpdateCrawlTableDataForCityIdAndCityName(List<EbaseInfo> list) throws Exception;
	 /*******************************修改股东数据 结束*********************************************************/
	 
	 /*******************************删除股东数据 结束*********************************************************/
	 /*******************************删除股东数据 结束*********************************************************/
}